{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6e7def8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python3_Jupyter_Nb_Pd&Spark_讯捷集团政策(保卫)分析.ipynb\n",
    "# Create By GF 2023-12-25 13:37"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e83f1251",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import pyspark\n",
    "from pyspark.storagelevel import StorageLevel\n",
    "from pyspark.sql import SparkSession, Row\n",
    "from pyspark.sql.types import IntegerType, DateType\n",
    "from pyspark.sql.window import Window\n",
    "from pyspark.sql.functions import asc, avg, col, desc, format_string, lag, lead, lit, row_number, count, when\n",
    "# --------------------------------------------------\n",
    "from pyspark.ml.feature import Normalizer, VectorAssembler\n",
    "from pyspark.ml.linalg import DenseVector\n",
    "# --------------------------------------------------\n",
    "from pyspark.ml.regression import LinearRegression\n",
    "from pyspark.ml.classification import LogisticRegression"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "b81cb5b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "8ba9c49e",
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.rcParams[\"font.sans-serif\"] = [\"SimHei\"]\n",
    "plt.rcParams[\"axes.unicode_minus\"] = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2e2a26fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# PySpark 创建 Spark 会话(连接)。\n",
    "# --------------------------------------------------\n",
    "spark = SparkSession.builder.appName('Basic').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f41a86f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "SDF_Policy_Catalog = spark.read.option(\"header\",\"true\") \\\n",
    "                               .option(\"encoding\", \"gbk\") \\\n",
    "                               .csv(\"file:///C:\\\\GF-Datas\\\\CSV数据_讯捷集团_保卫政策门店目录_2023-02-17至2023-12-22.csv\")\n",
    "SDF_Assault_Data   = spark.read.option(\"header\",\"true\") \\\n",
    "                               .option(\"encoding\", \"gbk\") \\\n",
    "                               .csv(\"file:///C:\\\\GF-Datas\\\\CSV数据_讯捷集团_突击数据_集团1加5精英突击前后对比_2023-02-08至2023-12-13.csv\")\n",
    "SDF_Parade_Data    = spark.read.option(\"header\",\"true\") \\\n",
    "                               .option(\"encoding\", \"gbk\") \\\n",
    "                               .csv(\"file:///C:\\\\GF-Datas\\\\CSV数据_讯捷集团_阅兵突击前后数据_2023-04-02.csv\")\n",
    "SDF_XJB_Data       = spark.read.option(\"header\",\"true\") \\\n",
    "                               .option(\"encoding\", \"gbk\") \\\n",
    "                               .csv(\"file:///C:\\\\GF-Datas\\\\CSV数据_讯捷集团_2023讯捷杯挑战赛数据_2023-06-14.csv\")\n",
    "SDF_Docx_Data      = spark.read.option(\"header\",\"true\") \\\n",
    "                               .option(\"encoding\", \"gbk\") \\\n",
    "                               .csv(\"file:///C:\\\\GF-Datas\\\\Policy_Docx_Extract.csv\")\n",
    "# --------------------------------------------------\n",
    "SDF_Store_Profit   = spark.read.option(\"header\",\"true\") \\\n",
    "                               .option(\"encoding\", \"utf-8\") \\\n",
    "                               .csv(\"file:///C:\\\\GF-Datas\\\\CSV数据_讯捷集团_毛利数据_门店毛利数据_2020-01-01至2023-12-31.csv\") \\\n",
    "                               .withColumnRenamed(\"xj_date\"             ,\"日期\") \\\n",
    "                               .withColumnRenamed(\"xj_year_month\"       ,\"年月\") \\\n",
    "                               .withColumnRenamed(\"xj_year\"             ,\"年\") \\\n",
    "                               .withColumnRenamed(\"xj_month\"            ,\"月\") \\\n",
    "                               .withColumnRenamed(\"xj_year_half\"        ,\"半年\") \\\n",
    "                               .withColumnRenamed(\"xj_week_sn\"          ,\"周序列\") \\\n",
    "                               .withColumnRenamed(\"xj_week_what_day\"    ,\"周内第几天\") \\\n",
    "                               .withColumnRenamed(\"xj_date_level\"       ,\"日期等级\") \\\n",
    "                               .withColumnRenamed(\"xj_day_level_name\"   ,\"日等级名称\") \\\n",
    "                               .withColumnRenamed(\"xj_ent_system\"       ,\"企业体系\") \\\n",
    "                               .withColumnRenamed(\"xj_ent_depart\"       ,\"企业部门\") \\\n",
    "                               .withColumnRenamed(\"xj_ent_bo\"           ,\"企业分公司\") \\\n",
    "                               .withColumnRenamed(\"xj_ent_center\"       ,\"企业中心\") \\\n",
    "                               .withColumnRenamed(\"xj_sto_id\"           ,\"门店ID\") \\\n",
    "                               .withColumnRenamed(\"xj_sto_name\"         ,\"门店名称\") \\\n",
    "                               .withColumnRenamed(\"xj_sto_level\"        ,\"门店等级\") \\\n",
    "                               .withColumnRenamed(\"xj_sto_status\"       ,\"门店状态\") \\\n",
    "                               .withColumnRenamed(\"xj_sto_open\"         ,\"门店开店日期\") \\\n",
    "                               .withColumnRenamed(\"xj_sto_close\"        ,\"门店闭店日期\") \\\n",
    "                               .withColumnRenamed(\"xj_sto_close_deco\"   ,\"门店闭店装修\") \\\n",
    "                               .withColumnRenamed(\"xj_sto_cls_dec_days\" ,\"门店闭店装修天数\") \\\n",
    "                               .withColumnRenamed(\"xj_region_class\"     ,\"区域类别\") \\\n",
    "                               .withColumnRenamed(\"xj_region_biz_id\"    ,\"区域商圈ID\") \\\n",
    "                               .withColumnRenamed(\"xj_region_biz\"       ,\"区域商圈\") \\\n",
    "                               .withColumnRenamed(\"xj_gr_p\"             ,\"毛利\") \\\n",
    "                               .withColumnRenamed(\"xj_gr_p_md_avg\"      ,\"毛利月日均\") \\\n",
    "                               .withColumnRenamed(\"xj_gr_p_yd_avg\"      ,\"毛利年日均\") \\\n",
    "                               .withColumnRenamed(\"xj_gr_p_m_sum\"       ,\"毛利月合计\") \\\n",
    "                               .withColumnRenamed(\"xj_gr_p_y_sum\"       ,\"毛利年合计\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ea06f2f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "SDF_Store_Profit = SDF_Store_Profit.withColumn(\"毛利(取整)\", col(\"毛利\") - (col(\"毛利\") % 500))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "dd1d0fe1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- 部门: string (nullable = true)\n",
      " |-- 门店简称: string (nullable = true)\n",
      " |-- 门店类别: string (nullable = true)\n",
      " |-- 突击时间: string (nullable = true)\n",
      " |-- 开始日期: string (nullable = true)\n",
      " |-- 结束日期: string (nullable = true)\n",
      " |-- 突击天数: string (nullable = true)\n",
      " |-- 队长: string (nullable = true)\n",
      " |-- 门店全称: string (nullable = true)\n",
      " |-- 项目: string (nullable = true)\n",
      " |-- 突击中日均(平时): string (nullable = true)\n",
      " |-- 突击中日均(周末): string (nullable = true)\n",
      " |-- 突击中日均(所有): string (nullable = true)\n",
      " |-- 突击前日均(平时): string (nullable = true)\n",
      " |-- 突击前日均(周末): string (nullable = true)\n",
      " |-- 突击前日均(所有): string (nullable = true)\n",
      " |-- 实际增长: string (nullable = true)\n",
      " |-- 相对增长: string (nullable = true)\n",
      " |-- 相对涨幅: string (nullable = true)\n",
      " |-- 门店涨幅: string (nullable = true)\n",
      " |-- 体系突击前: string (nullable = true)\n",
      " |-- 体系突击后: string (nullable = true)\n",
      " |-- 大盘涨幅: string (nullable = true)\n",
      " |-- 体系突击前(所有门店): string (nullable = true)\n",
      " |-- 体系突击后(所有门店): string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Spark Process : Display Schema.\n",
    "# --------------------------------------------------\n",
    "SDF_Assault_Data.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "20a076ab",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Spark Process : SparkSQL DataFrame to Pandas DataFrame.\n",
    "# --------------------------------------------------\n",
    "PDF_Policy_Catalog = SDF_Policy_Catalog.toPandas()\n",
    "PDF_Assault_Data   = SDF_Assault_Data.toPandas()\n",
    "PDF_Parade_Data    = SDF_Parade_Data.toPandas()\n",
    "PDF_XJB_Data       = SDF_XJB_Data.toPandas()\n",
    "PDF_Store_Profit   = SDF_Store_Profit.toPandas()\n",
    "# --------------------------------------------------\n",
    "# End of Part."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "55f38c8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pandas Process : Modifying Field Data Type.\n",
    "# --------------------------------------------------\n",
    "PDF_Policy_Catalog[\"开始时间(保卫)\"] = PDF_Policy_Catalog[\"开始时间(保卫)\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "PDF_Policy_Catalog[\"结束时间(保卫)\"] = PDF_Policy_Catalog[\"结束时间(保卫)\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "# --------------------------------------------------\n",
    "PDF_Assault_Data[\"开始日期\"] = PDF_Assault_Data[\"开始日期\"].astype(\"datetime64[ns]\")\n",
    "PDF_Assault_Data[\"结束日期\"] = PDF_Assault_Data[\"结束日期\"].astype(\"datetime64[ns]\")\n",
    "PDF_Assault_Data[\"突击中日均(所有)\"] = PDF_Assault_Data[\"突击中日均(所有)\"].astype(\"float64\")\n",
    "PDF_Assault_Data[\"突击中日均(周末)\"] = PDF_Assault_Data[\"突击中日均(周末)\"].astype(\"float64\")\n",
    "# --------------------------------------------------\n",
    "PDF_Parade_Data[\"开始日期\"] = PDF_Parade_Data[\"开始日期\"].astype(\"datetime64[ns]\")\n",
    "PDF_Parade_Data[\"结束日期\"] = PDF_Parade_Data[\"结束日期\"].astype(\"datetime64[ns]\")\n",
    "PDF_Parade_Data[\"日均毛利(阅兵中)\"] = PDF_Parade_Data[\"日均毛利(阅兵中)\"].astype(\"float64\")\n",
    "# --------------------------------------------------\n",
    "PDF_XJB_Data[\"开始日期\"] = PDF_XJB_Data[\"开始日期\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "PDF_XJB_Data[\"结束日期\"] = PDF_XJB_Data[\"结束日期\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "# --------------------------------------------------\n",
    "PDF_Docx_Data = SDF_Docx_Data.toPandas()\n",
    "PDF_Docx_Data[\"开始时间\"] = PDF_Docx_Data[\"开始时间\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "PDF_Docx_Data[\"结束时间\"] = PDF_Docx_Data[\"结束时间\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "PDF_Docx_Data[\"政策目标\"] = PDF_Docx_Data[\"政策目标\"].astype(\"float64\")\n",
    "# --------------------------------------------------\n",
    "PDF_Store_Profit[\"日期\"] = PDF_Store_Profit[\"日期\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "PDF_Store_Profit[\"门店开店日期\"] = PDF_Store_Profit[\"门店开店日期\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "PDF_Store_Profit[\"门店闭店日期\"] = PDF_Store_Profit[\"门店闭店日期\"].astype(\"datetime64[ns]\").dt.strftime(\"%Y-%m-%d\")\n",
    "PDF_Store_Profit[\"毛利\"] = PDF_Store_Profit[\"毛利\"].astype(\"float64\")\n",
    "# --------------------------------------------------\n",
    "# End of Part."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d02c36bb",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "PDF_Policy_Catalog[\"对照类型\"] = None\n",
    "PDF_Policy_Catalog[\"对照日期(开始)\"] = None\n",
    "PDF_Policy_Catalog[\"对照日期(结束)\"] = None\n",
    "PDF_Policy_Catalog[\"对照毛利(日均)\"] = None\n",
    "PDF_Policy_Catalog[\"对照毛利(周末)\"] = None\n",
    "# --------------------------------------------------\n",
    "for Left_Idx in PDF_Policy_Catalog.index: # -> 对应政策Docx文件数据.\n",
    "    Left_Store_Name      = PDF_Policy_Catalog.loc[Left_Idx, \"门店名称\"]\n",
    "    Left_Bgn_Date_Secure = PDF_Policy_Catalog.loc[Left_Idx, \"开始时间(保卫)\"]\n",
    "    for Right_Idx in PDF_Docx_Data.index:\n",
    "        Right_Store_Name   = PDF_Docx_Data.loc[Right_Idx, \"门店全称\"]\n",
    "        Right_Bgn_Date_Docx = PDF_Docx_Data.loc[Right_Idx, \"开始时间\"]\n",
    "        Right_End_Date_Docx = PDF_Docx_Data.loc[Right_Idx, \"结束时间\"]\n",
    "        # ------------------------------------------\n",
    "        TimeDelta = (Left_Bgn_Date_Secure - Right_End_Date_Docx).days\n",
    "        # ------------------------------------------\n",
    "        if (Left_Store_Name == Right_Store_Name) and (0 <= TimeDelta) and (TimeDelta <= 14):\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照类型\"] = \"Docx文档\"\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照日期(开始)\"] = Right_Bgn_Date_Docx.strftime(\"%Y-%m-%d\")\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照日期(结束)\"] = Right_End_Date_Docx.strftime(\"%Y-%m-%d\")\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照毛利(日均)\"] = PDF_Docx_Data.loc[Right_Idx, \"政策目标\"]\n",
    "# --------------------------------------------------\n",
    "for Left_Idx in PDF_Policy_Catalog.index: # -> 对应1加5突击数据.\n",
    "    Left_Store_Name      = PDF_Policy_Catalog.loc[Left_Idx, \"门店名称\"]\n",
    "    Left_Bgn_Date_Secure = PDF_Policy_Catalog.loc[Left_Idx, \"开始时间(保卫)\"]\n",
    "    for Right_Idx in PDF_Assault_Data.index:\n",
    "        Right_Store_Name       = PDF_Assault_Data.loc[Right_Idx, \"门店全称\"]\n",
    "        Right_Bgn_Date_Assault = PDF_Assault_Data.loc[Right_Idx, \"开始日期\"]\n",
    "        Right_End_Date_Assault = PDF_Assault_Data.loc[Right_Idx, \"结束日期\"]\n",
    "        # ------------------------------------------\n",
    "        TimeDelta = (Left_Bgn_Date_Secure - Right_End_Date_Assault).days\n",
    "        # ------------------------------------------\n",
    "        if (Left_Store_Name == Right_Store_Name) and (0 <= TimeDelta) and (TimeDelta <= 7):\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照类型\"] = \"1加5突击\"\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照日期(开始)\"] = Right_Bgn_Date_Assault.strftime(\"%Y-%m-%d\")\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照日期(结束)\"] = Right_End_Date_Assault.strftime(\"%Y-%m-%d\")\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照毛利(日均)\"] = PDF_Assault_Data.loc[Right_Idx, \"突击中日均(所有)\"]\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照毛利(周末)\"] = PDF_Assault_Data.loc[Right_Idx, \"突击中日均(周末)\"]\n",
    "# --------------------------------------------------\n",
    "for Left_Idx in PDF_Policy_Catalog.index: # -> 对应阅兵突击数据.\n",
    "    Left_Store_Name      = PDF_Policy_Catalog.loc[Left_Idx, \"门店名称\"]\n",
    "    Left_Bgn_Date_Secure = PDF_Policy_Catalog.loc[Left_Idx, \"开始时间(保卫)\"]\n",
    "    for Right_Idx in PDF_Parade_Data.index:\n",
    "        Right_Store_Name      = PDF_Parade_Data.loc[Right_Idx, \"门店全称\"]\n",
    "        Right_Bgn_Date_Parade = PDF_Parade_Data.loc[Right_Idx, \"开始日期\"]\n",
    "        Right_End_Date_Parade = PDF_Parade_Data.loc[Right_Idx, \"结束日期\"]\n",
    "        # ------------------------------------------\n",
    "        TimeDelta = (Left_Bgn_Date_Secure - Right_End_Date_Parade).days\n",
    "        # ------------------------------------------\n",
    "        if (Left_Store_Name == Right_Store_Name) and (0 <= TimeDelta) and (TimeDelta <= 7):\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照类型\"] = \"阅兵突击\"\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照日期(开始)\"] = Right_Bgn_Date_Parade.strftime(\"%Y-%m-%d\")\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照日期(结束)\"] = Right_End_Date_Parade.strftime(\"%Y-%m-%d\")\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照毛利(日均)\"] = PDF_Parade_Data.loc[Right_Idx, \"日均毛利(阅兵中)\"]\n",
    "# --------------------------------------------------\n",
    "for Left_Idx in PDF_Policy_Catalog.index: # -> 对应讯捷杯数据.\n",
    "    Left_Store_Name      = PDF_Policy_Catalog.loc[Left_Idx, \"门店名称\"]\n",
    "    Left_Bgn_Date_Secure = PDF_Policy_Catalog.loc[Left_Idx, \"开始时间(保卫)\"]\n",
    "    for Right_Idx in PDF_XJB_Data.index:\n",
    "        Right_Store_Name   = PDF_XJB_Data.loc[Right_Idx, \"门店全称\"]\n",
    "        Right_Bgn_Date_XJB = PDF_XJB_Data.loc[Right_Idx, \"开始日期\"]\n",
    "        Right_End_Date_XJB = PDF_XJB_Data.loc[Right_Idx, \"结束日期\"]\n",
    "        # ------------------------------------------\n",
    "        TimeDelta = (Left_Bgn_Date_Secure - Right_End_Date_XJB).days\n",
    "        # ------------------------------------------\n",
    "        if (Left_Store_Name == Right_Store_Name) and (0 <= TimeDelta) and (TimeDelta <= 14):\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照类型\"] = \"讯捷杯挑战赛\"\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照日期(开始)\"] = Right_Bgn_Date_XJB.strftime(\"%Y-%m-%d\")\n",
    "            PDF_Policy_Catalog.loc[Left_Idx, \"对照日期(结束)\"] = Right_End_Date_XJB.strftime(\"%Y-%m-%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dec63855",
   "metadata": {},
   "outputs": [],
   "source": [
    "PDF_Policy_Catalog[\"实际毛利(所有日均)\"] = None\n",
    "PDF_Policy_Catalog[\"实际毛利(周末日均)\"] = None\n",
    "# --------------------------------------------------\n",
    "for Idx in PDF_Policy_Catalog.index: # -> 拉取保卫期间实际毛利(日均).\n",
    "    Store_Name      = PDF_Policy_Catalog.loc[Idx, \"门店名称\"]\n",
    "    Bgn_Date_Secure = PDF_Policy_Catalog.loc[Idx, \"开始时间(保卫)\"]\n",
    "    End_Date_Secure = PDF_Policy_Catalog.loc[Idx, \"结束时间(保卫)\"]\n",
    "    # ----------------------------------------------\n",
    "    Result_Normal  = PDF_Store_Profit[PDF_Store_Profit[\"门店名称\"] == Store_Name]\n",
    "    Result_Normal  = Result_Normal[(Bgn_Date_Secure <= Result_Normal[\"日期\"]) & (Result_Normal[\"日期\"] <= End_Date_Secure)]\n",
    "    Result_Weekend = Result_Normal[(5 <= Result_Normal[\"日期\"].dt.weekday) & (Result_Normal[\"日期\"].dt.weekday <= 6)]\n",
    "    # ----------------------------------------------\n",
    "    PDF_Policy_Catalog.loc[Idx, \"实际毛利(所有日均)\"] = round(Result_Normal[\"毛利\"].mean(), 4)\n",
    "    PDF_Policy_Catalog.loc[Idx, \"实际毛利(周末日均)\"] = round(Result_Weekend[\"毛利\"].mean(), 4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b2cb8225",
   "metadata": {},
   "outputs": [],
   "source": [
    "PDF_Policy_Catalog[\"保卫效率(所有日均)\"] = None\n",
    "PDF_Policy_Catalog[\"保卫效率(周末日均)\"] = None\n",
    "# --------------------------------------------------\n",
    "for Idx in PDF_Policy_Catalog.index: # -> 拉取保卫期间实际毛利.\n",
    "    Compare_Normal   = PDF_Policy_Catalog.loc[Idx, \"对照毛利(日均)\"]\n",
    "    Compare_Weekend  = PDF_Policy_Catalog.loc[Idx, \"对照毛利(周末)\"]\n",
    "    Actually_Normal  = PDF_Policy_Catalog.loc[Idx, \"实际毛利(所有日均)\"]\n",
    "    Actually_Weekend = PDF_Policy_Catalog.loc[Idx, \"实际毛利(周末日均)\"]\n",
    "    # ----------------------------------------------\n",
    "    if (Compare_Normal != None) and (Actually_Normal != None):\n",
    "        PDF_Policy_Catalog.loc[Idx, \"保卫效率(所有日均)\"] = round(Actually_Normal / Compare_Normal, 4)\n",
    "    if (Compare_Weekend != None) and (Actually_Weekend != None):\n",
    "        PDF_Policy_Catalog.loc[Idx, \"保卫效率(周末日均)\"] = round(Actually_Weekend / Compare_Weekend, 4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7a56d54",
   "metadata": {},
   "outputs": [],
   "source": [
    "PDF_Policy_Catalog[\"保卫效率(综合)\"] = None\n",
    "# --------------------------------------------------\n",
    "for Idx in PDF_Policy_Catalog.index: # -> 拉取保卫期间实际毛利.\n",
    "    Weight:list   = [0.0, 0.0]\n",
    "    Value_All     = PDF_Policy_Catalog.loc[Idx, \"保卫效率(所有日均)\"]\n",
    "    Value_Weekend = PDF_Policy_Catalog.loc[Idx, \"保卫效率(周末日均)\"]\n",
    "    # ----------------------------------------------\n",
    "    if (Value_All     != None): Weight[0] = 1\n",
    "    if (Value_Weekend != None): Weight[1] = 1.5\n",
    "    # ----------------------------------------------\n",
    "    if (Value_All     == None): Value_All = 0\n",
    "    if (Value_Weekend == None): Value_Weekend = 0\n",
    "    # ----------------------------------------------\n",
    "    if (sum(Weight)   !=  0.0): PDF_Policy_Catalog.loc[Idx, \"保卫效率(综合)\"] = \\\n",
    "                                (Value_All * Weight[0] + Value_Weekend * Weight[1]) / sum(Weight)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "582ad492",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Features</th>\n",
       "      <th>Factors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-12</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-10</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-09</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-08</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>C1</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>外围</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>二级</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>市区</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>郊县</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   Features  Factors\n",
       "0   2023-12        0\n",
       "1   2023-11        1\n",
       "2   2023-10        2\n",
       "3   2023-09        3\n",
       "4   2023-08        4\n",
       "..      ...      ...\n",
       "73       C1       72\n",
       "74       外围       73\n",
       "75       二级       74\n",
       "76       市区       75\n",
       "77       郊县       76\n",
       "\n",
       "[78 rows x 2 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Machine Learning : Features Factorized.\n",
    "# --------------------------------------------------\n",
    "Feature1 = PDF_Store_Profit[\"年月\"]\n",
    "Feature2 = PDF_Store_Profit[\"企业部门\"] # -> \"企业部门\" 含有地区信息, 如: 一部/贵州。\n",
    "Feature3 = PDF_Store_Profit[\"日期等级\"]\n",
    "Feature4 = PDF_Store_Profit[\"门店等级\"]\n",
    "Feature5 = PDF_Store_Profit[\"区域类别\"] # -> \"区域类别\" 含有区域特点, 如: 市区/郊县。\n",
    "# --------------------------------------------------\n",
    "Features = pd.concat([Feature1, Feature2, Feature3, Feature4, Feature5], join=\"outer\")\n",
    "# --------------------------------------------------\n",
    "Features = Features.drop_duplicates().reset_index(drop=True)\n",
    "# --------------------------------------------------\n",
    "Features = pd.DataFrame(data=Features, columns=([\"Features\"]))\n",
    "# --------------------------------------------------\n",
    "Features[\"Factors\"] = pd.factorize(Features[\"Features\"])[0] # -> pd.factorize 因子化。\n",
    "# --------------------------------------------------\n",
    "Features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "3f03a1d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>年月</th>\n",
       "      <th>年月(Fact)</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>企业部门(Fact)</th>\n",
       "      <th>日期等级</th>\n",
       "      <th>日期等级(Fact)</th>\n",
       "      <th>门店等级</th>\n",
       "      <th>门店等级(Fact)</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域类别(Fact)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-12</td>\n",
       "      <td>0</td>\n",
       "      <td>贵州</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>C</td>\n",
       "      <td>61</td>\n",
       "      <td>外围</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-12</td>\n",
       "      <td>0</td>\n",
       "      <td>贵州</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>B2</td>\n",
       "      <td>63</td>\n",
       "      <td>二级</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-12</td>\n",
       "      <td>0</td>\n",
       "      <td>贵州</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>B1</td>\n",
       "      <td>62</td>\n",
       "      <td>市区</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-12</td>\n",
       "      <td>0</td>\n",
       "      <td>贵州</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>B1</td>\n",
       "      <td>62</td>\n",
       "      <td>二级</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2023-12</td>\n",
       "      <td>0</td>\n",
       "      <td>贵州</td>\n",
       "      <td>48</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>C</td>\n",
       "      <td>61</td>\n",
       "      <td>郊县</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377800</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>47</td>\n",
       "      <td>重庆</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>B1</td>\n",
       "      <td>62</td>\n",
       "      <td>郊县</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377805</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>47</td>\n",
       "      <td>云南</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>B2</td>\n",
       "      <td>63</td>\n",
       "      <td>二级</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377806</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>47</td>\n",
       "      <td>重庆</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>A2</td>\n",
       "      <td>66</td>\n",
       "      <td>外围</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377810</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>47</td>\n",
       "      <td>重庆</td>\n",
       "      <td>55</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>B1</td>\n",
       "      <td>62</td>\n",
       "      <td>外围</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>377818</th>\n",
       "      <td>2020-01</td>\n",
       "      <td>47</td>\n",
       "      <td>云南</td>\n",
       "      <td>49</td>\n",
       "      <td>1</td>\n",
       "      <td>56</td>\n",
       "      <td>A2</td>\n",
       "      <td>66</td>\n",
       "      <td>二级</td>\n",
       "      <td>74</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>141598 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             年月  年月(Fact) 企业部门  企业部门(Fact) 日期等级  日期等级(Fact) 门店等级  门店等级(Fact)  \\\n",
       "0       2023-12         0   贵州          48    1          56    C          61   \n",
       "2       2023-12         0   贵州          48    1          56   B2          63   \n",
       "3       2023-12         0   贵州          48    1          56   B1          62   \n",
       "4       2023-12         0   贵州          48    1          56   B1          62   \n",
       "5       2023-12         0   贵州          48    1          56    C          61   \n",
       "...         ...       ...  ...         ...  ...         ...  ...         ...   \n",
       "377800  2020-01        47   重庆          55    1          56   B1          62   \n",
       "377805  2020-01        47   云南          49    1          56   B2          63   \n",
       "377806  2020-01        47   重庆          55    1          56   A2          66   \n",
       "377810  2020-01        47   重庆          55    1          56   B1          62   \n",
       "377818  2020-01        47   云南          49    1          56   A2          66   \n",
       "\n",
       "       区域类别  区域类别(Fact)  \n",
       "0        外围          73  \n",
       "2        二级          74  \n",
       "3        市区          75  \n",
       "4        二级          74  \n",
       "5        郊县          76  \n",
       "...     ...         ...  \n",
       "377800   郊县          76  \n",
       "377805   二级          74  \n",
       "377806   外围          73  \n",
       "377810   外围          73  \n",
       "377818   二级          74  \n",
       "\n",
       "[141598 rows x 10 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Machine Learning : Adding Feature Factors to The Original Table.\n",
    "# --------------------------------------------------\n",
    "PDF_Store_Profit_ML = pd.merge(left=PDF_Store_Profit,    right=Features, how=\"left\", left_on=\"年月\",     right_on=\"Features\")\n",
    "PDF_Store_Profit_ML = PDF_Store_Profit_ML.rename(columns={\"Factors\":\"年月(Fact)\"}).drop(\"Features\", axis=1)\n",
    "# --------------------------------------------------\n",
    "PDF_Store_Profit_ML = pd.merge(left=PDF_Store_Profit_ML, right=Features, how=\"left\", left_on=\"企业部门\", right_on=\"Features\")\n",
    "PDF_Store_Profit_ML = PDF_Store_Profit_ML.rename(columns={\"Factors\":\"企业部门(Fact)\"}).drop(\"Features\", axis=1)\n",
    "# --------------------------------------------------\n",
    "PDF_Store_Profit_ML = pd.merge(left=PDF_Store_Profit_ML, right=Features, how=\"left\", left_on=\"日期等级\", right_on=\"Features\")\n",
    "PDF_Store_Profit_ML = PDF_Store_Profit_ML.rename(columns={\"Factors\":\"日期等级(Fact)\"}).drop(\"Features\", axis=1)\n",
    "# --------------------------------------------------\n",
    "PDF_Store_Profit_ML = pd.merge(left=PDF_Store_Profit_ML, right=Features, how=\"left\", left_on=\"门店等级\", right_on=\"Features\")\n",
    "PDF_Store_Profit_ML = PDF_Store_Profit_ML.rename(columns={\"Factors\":\"门店等级(Fact)\"}).drop(\"Features\", axis=1)\n",
    "# --------------------------------------------------\n",
    "PDF_Store_Profit_ML = pd.merge(left=PDF_Store_Profit_ML, right=Features, how=\"left\", left_on=\"区域类别\", right_on=\"Features\")\n",
    "PDF_Store_Profit_ML = PDF_Store_Profit_ML.rename(columns={\"Factors\":\"区域类别(Fact)\"}).drop(\"Features\", axis=1)\n",
    "# --------------------------------------------------\n",
    "PDF_Store_Profit_NA = PDF_Store_Profit_ML[PDF_Store_Profit_ML[\"毛利(取整)\"].isna() == True]\n",
    "PDF_Store_Profit_NA = PDF_Store_Profit_NA.replace(pd.NA, \"\")\n",
    "SDF_Store_Profit_NA = spark.createDataFrame(PDF_Store_Profit_NA)\n",
    "# --------------------------------------------------\n",
    "AverageY      = PDF_Store_Profit_ML[\"毛利\"].mean()\n",
    "Left_Bond     = AverageY * (1 - 0.5)\n",
    "Right_Bond    = AverageY * (1 + 0.5)\n",
    "DropNA_SubSet = [\"年月\", \"企业部门\", \"日期等级\", \"门店等级\", \"区域类别\", \"毛利\"]\n",
    "PDF_Store_Profit_ML = PDF_Store_Profit_ML.dropna(axis= 0, how=\"any\", subset=DropNA_SubSet)\n",
    "PDF_Store_Profit_ML = PDF_Store_Profit_ML.replace(pd.NA, \"\") # -> TypeError: field 门店开店日期: Can not merge type <class '...Type...\n",
    "PDF_Store_Profit_ML = PDF_Store_Profit_ML[(Left_Bond <= PDF_Store_Profit_ML[\"毛利\"]) & (PDF_Store_Profit_ML[\"毛利\"] <= Right_Bond)]\n",
    "SDF_Store_Profit_ML = spark.createDataFrame(PDF_Store_Profit_ML) # -> Pandas DataFrame to SparkSQL DataFrame.\n",
    "# --------------------------------------------------\n",
    "PDF_Store_Profit_ML[[\"年月\",     \"年月(Fact)\",\n",
    "                     \"企业部门\", \"企业部门(Fact)\",\n",
    "                     \"日期等级\", \"日期等级(Fact)\",\n",
    "                     \"门店等级\", \"门店等级(Fact)\",\n",
    "                     \"区域类别\", \"区域类别(Fact)\"]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "194c5166",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------+\n",
      "|            features|   label|\n",
      "+--------------------+--------+\n",
      "|[0.0,48.0,56.0,61...|5463.482|\n",
      "|[0.0,48.0,56.0,63...|7818.307|\n",
      "|[0.0,48.0,56.0,62...| 9307.56|\n",
      "|[0.0,48.0,56.0,62...| 5589.32|\n",
      "|[0.0,48.0,56.0,61...|5289.009|\n",
      "+--------------------+--------+\n",
      "only showing top 5 rows\n",
      "\n",
      "Root Mean Squared Error: 2455.192835\n",
      "R-squared: 0.076318\n"
     ]
    }
   ],
   "source": [
    "# 线性回归 Step 1: 将自变量和因变量分开。\n",
    "# --------------------------------------------------\n",
    "Assembler = VectorAssembler(inputCols=[\"年月(Fact)\", \"企业部门(Fact)\", \"日期等级(Fact)\", \"门店等级(Fact)\", \"区域类别(Fact)\"],\n",
    "                            outputCol=\"features\") # -> 组装特征\n",
    "# --------------------------------------------------\n",
    "Data_for_Reg = Assembler.transform(SDF_Store_Profit_ML)\n",
    "# --------------------------------------------------\n",
    "Data_for_Reg = Data_for_Reg.withColumn(\"label\", col(\"毛利\"))\n",
    "# --------------------------------------------------\n",
    "Data_for_Reg = Data_for_Reg.select([\"features\", \"label\"]) # -> 选择特征向量和因变量列.\n",
    "# --------------------------------------------------\n",
    "Data_for_Reg.show(5)\n",
    "\n",
    "# 线性回归 Step 2: 构建线性模型。\n",
    "# --------------------------------------------------\n",
    "LR = LinearRegression()\n",
    "\n",
    "# 线性回归 Step 2: 训练线性模型。\n",
    "# --------------------------------------------------\n",
    "Model = LR.fit(Data_for_Reg)\n",
    "\n",
    "# 线性回归 Step 4: 评估线性模型。\n",
    "# --------------------------------------------------\n",
    "Summary = Model.summary\n",
    "# --------------------------------------------------\n",
    "print(\"Root Mean Squared Error: %f\" % Summary.rootMeanSquaredError)\n",
    "print(\"R-squared: %f\" % Summary.r2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "6e59a008",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "预测值: 2747.668491\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>年月</th>\n",
       "      <th>年</th>\n",
       "      <th>月</th>\n",
       "      <th>半年</th>\n",
       "      <th>周序列</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>日期等级</th>\n",
       "      <th>日等级名称</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>...</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月日均</th>\n",
       "      <th>毛利年日均</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>毛利年合计</th>\n",
       "      <th>日期(Fact)</th>\n",
       "      <th>企业部门(Fact)</th>\n",
       "      <th>门店名称(Fact)</th>\n",
       "      <th>门店等级(Fact)</th>\n",
       "      <th>区域类别(Fact)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>377814</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>工作日</td>\n",
       "      <td>华为</td>\n",
       "      <td>...</td>\n",
       "      <td>112.02</td>\n",
       "      <td>2526.988309</td>\n",
       "      <td>2992.262726</td>\n",
       "      <td>78336.6376</td>\n",
       "      <td>1083199.1069</td>\n",
       "      <td>1460</td>\n",
       "      <td>1462</td>\n",
       "      <td>1718</td>\n",
       "      <td>1739</td>\n",
       "      <td>1752</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                日期       年月     年  月 半年 周序列 周内第几天 日期等级 日等级名称 企业体系  ...  \\\n",
       "377814  2020-01-31  2020-01  2020  1  1   5     6    1   工作日   华为  ...   \n",
       "\n",
       "            毛利        毛利月日均        毛利年日均       毛利月合计         毛利年合计 日期(Fact)  \\\n",
       "377814  112.02  2526.988309  2992.262726  78336.6376  1083199.1069     1460   \n",
       "\n",
       "       企业部门(Fact) 门店名称(Fact) 门店等级(Fact) 区域类别(Fact)  \n",
       "377814       1462       1718       1739       1752  \n",
       "\n",
       "[1 rows x 34 columns]"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"预测值: %f\" % Model.predict(DenseVector([1460, 1462, 1718, 1739, 1752])))\n",
    "PDF_Store_Profit_ML[(PDF_Store_Profit_ML[\"日期\"] == \"2020-01-31\") & (PDF_Store_Profit_ML[\"门店名称\"] == \"云南曲靖万达华为店\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "e07ea1aa",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "预测值: 6277.409728\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>年月</th>\n",
       "      <th>年</th>\n",
       "      <th>月</th>\n",
       "      <th>半年</th>\n",
       "      <th>周序列</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>日期等级</th>\n",
       "      <th>日等级名称</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>...</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月日均</th>\n",
       "      <th>毛利年日均</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>毛利年合计</th>\n",
       "      <th>日期(Fact)</th>\n",
       "      <th>企业部门(Fact)</th>\n",
       "      <th>门店名称(Fact)</th>\n",
       "      <th>门店等级(Fact)</th>\n",
       "      <th>区域类别(Fact)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>377816</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>工作日</td>\n",
       "      <td>形象</td>\n",
       "      <td>...</td>\n",
       "      <td>2898.86</td>\n",
       "      <td>7434.264122</td>\n",
       "      <td>5084.111525</td>\n",
       "      <td>230462.1878</td>\n",
       "      <td>1820111.926</td>\n",
       "      <td>1460</td>\n",
       "      <td>1468</td>\n",
       "      <td>1729</td>\n",
       "      <td>1741</td>\n",
       "      <td>1754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                日期       年月     年  月 半年 周序列 周内第几天 日期等级 日等级名称 企业体系  ...  \\\n",
       "377816  2020-01-31  2020-01  2020  1  1   5     6    1   工作日   形象  ...   \n",
       "\n",
       "             毛利        毛利月日均        毛利年日均        毛利月合计        毛利年合计 日期(Fact)  \\\n",
       "377816  2898.86  7434.264122  5084.111525  230462.1878  1820111.926     1460   \n",
       "\n",
       "       企业部门(Fact) 门店名称(Fact) 门店等级(Fact) 区域类别(Fact)  \n",
       "377816       1468       1729       1741       1754  \n",
       "\n",
       "[1 rows x 34 columns]"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"预测值: %f\" % Model.predict(DenseVector([1460, 1468, 1729, 1741, 1754])))\n",
    "PDF_Store_Profit_ML[(PDF_Store_Profit_ML[\"日期\"] == \"2020-01-31\") & (PDF_Store_Profit_ML[\"门店名称\"] == \"重庆綦江万达形象店\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "92d08447",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "预测值: 12307.039543\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>年月</th>\n",
       "      <th>年</th>\n",
       "      <th>月</th>\n",
       "      <th>半年</th>\n",
       "      <th>周序列</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>日期等级</th>\n",
       "      <th>日等级名称</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>...</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月日均</th>\n",
       "      <th>毛利年日均</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>毛利年合计</th>\n",
       "      <th>日期(Fact)</th>\n",
       "      <th>企业部门(Fact)</th>\n",
       "      <th>门店名称(Fact)</th>\n",
       "      <th>门店等级(Fact)</th>\n",
       "      <th>区域类别(Fact)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>377818</th>\n",
       "      <td>2020-01-31</td>\n",
       "      <td>2020-01</td>\n",
       "      <td>2020</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>工作日</td>\n",
       "      <td>形象</td>\n",
       "      <td>...</td>\n",
       "      <td>6769.22</td>\n",
       "      <td>13027.919809</td>\n",
       "      <td>12272.19811</td>\n",
       "      <td>403865.5141</td>\n",
       "      <td>4491624.5083</td>\n",
       "      <td>1460</td>\n",
       "      <td>1462</td>\n",
       "      <td>1675</td>\n",
       "      <td>1744</td>\n",
       "      <td>1752</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                日期       年月     年  月 半年 周序列 周内第几天 日期等级 日等级名称 企业体系  ...  \\\n",
       "377818  2020-01-31  2020-01  2020  1  1   5     6    1   工作日   形象  ...   \n",
       "\n",
       "             毛利         毛利月日均        毛利年日均        毛利月合计         毛利年合计  \\\n",
       "377818  6769.22  13027.919809  12272.19811  403865.5141  4491624.5083   \n",
       "\n",
       "       日期(Fact) 企业部门(Fact) 门店名称(Fact) 门店等级(Fact) 区域类别(Fact)  \n",
       "377818     1460       1462       1675       1744       1752  \n",
       "\n",
       "[1 rows x 34 columns]"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"预测值: %f\" % Model.predict(DenseVector([1460, 1462, 1675, 1744, 1752])))\n",
    "PDF_Store_Profit_ML[(PDF_Store_Profit_ML[\"日期\"] == \"2020-01-31\") & (PDF_Store_Profit_ML[\"门店名称\"] == \"云南玉溪凤凰路形象店\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c2e3622",
   "metadata": {},
   "outputs": [],
   "source": [
    "#PDF_Policy_Catalog.to_excel(\"./保卫政策完成情况.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "id": "cc92521f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 按日期进行抽样"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "411a8c57",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>日期(Fact)</th>\n",
       "      <th>企业部门</th>\n",
       "      <th>企业部门(Fact)</th>\n",
       "      <th>门店名称</th>\n",
       "      <th>门店名称(Fact)</th>\n",
       "      <th>门店等级</th>\n",
       "      <th>门店等级(Fact)</th>\n",
       "      <th>区域类别</th>\n",
       "      <th>区域类别(Fact)</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月日均</th>\n",
       "      <th>毛利年日均</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>毛利年合计</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44648</th>\n",
       "      <td>2023-06-09</td>\n",
       "      <td>192</td>\n",
       "      <td>四部</td>\n",
       "      <td>1467</td>\n",
       "      <td>四川青白江青江北路华为店</td>\n",
       "      <td>1589</td>\n",
       "      <td>B1</td>\n",
       "      <td>1740</td>\n",
       "      <td>郊县</td>\n",
       "      <td>1754</td>\n",
       "      <td>16041.0592</td>\n",
       "      <td>7808.442093</td>\n",
       "      <td>7576.399987</td>\n",
       "      <td>234253.2628</td>\n",
       "      <td>2765385.9954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>258934</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>1020</td>\n",
       "      <td>重庆</td>\n",
       "      <td>1468</td>\n",
       "      <td>重庆南川形象店</td>\n",
       "      <td>1693</td>\n",
       "      <td>C</td>\n",
       "      <td>1739</td>\n",
       "      <td>郊县</td>\n",
       "      <td>1754</td>\n",
       "      <td>4584.4220</td>\n",
       "      <td>4155.305867</td>\n",
       "      <td>4733.694515</td>\n",
       "      <td>128814.4819</td>\n",
       "      <td>1727798.4983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>197503</th>\n",
       "      <td>2021-10-03</td>\n",
       "      <td>793</td>\n",
       "      <td>二部</td>\n",
       "      <td>1463</td>\n",
       "      <td>四川崇州昌明路形象店</td>\n",
       "      <td>1651</td>\n",
       "      <td>A1</td>\n",
       "      <td>1743</td>\n",
       "      <td>郊县</td>\n",
       "      <td>1754</td>\n",
       "      <td>8357.1199</td>\n",
       "      <td>10913.150538</td>\n",
       "      <td>8675.900117</td>\n",
       "      <td>338307.6667</td>\n",
       "      <td>3166703.5429</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                日期  日期(Fact) 企业部门  企业部门(Fact)          门店名称  门店名称(Fact) 门店等级  \\\n",
       "44648   2023-06-09       192   四部        1467  四川青白江青江北路华为店        1589   B1   \n",
       "258934  2021-03-16      1020   重庆        1468       重庆南川形象店        1693    C   \n",
       "197503  2021-10-03       793   二部        1463    四川崇州昌明路形象店        1651   A1   \n",
       "\n",
       "        门店等级(Fact) 区域类别  区域类别(Fact)          毛利         毛利月日均        毛利年日均  \\\n",
       "44648         1740   郊县        1754  16041.0592   7808.442093  7576.399987   \n",
       "258934        1739   郊县        1754   4584.4220   4155.305867  4733.694515   \n",
       "197503        1743   郊县        1754   8357.1199  10913.150538  8675.900117   \n",
       "\n",
       "              毛利月合计         毛利年合计  \n",
       "44648   234253.2628  2765385.9954  \n",
       "258934  128814.4819  1727798.4983  \n",
       "197503  338307.6667  3166703.5429  "
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "FieldLabels = [\"日期\",       \"日期(Fact)\",     \"企业部门\", \"企业部门(Fact)\", \"门店名称\", \"门店名称(Fact)\",\n",
    "               \"门店等级\",   \"门店等级(Fact)\", \"区域类别\", \"区域类别(Fact)\", \"毛利\",     \"毛利月日均\",\n",
    "               \"毛利年日均\", \"毛利月合计\",     \"毛利年合计\"]\n",
    "PDF_Store_Profit_ML.sample(3)[FieldLabels]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "id": "288d3f51",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "预测值: 7645.451469\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>年月</th>\n",
       "      <th>年</th>\n",
       "      <th>月</th>\n",
       "      <th>半年</th>\n",
       "      <th>周序列</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>日期等级</th>\n",
       "      <th>日等级名称</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>...</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月日均</th>\n",
       "      <th>毛利年日均</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>毛利年合计</th>\n",
       "      <th>日期(Fact)</th>\n",
       "      <th>企业部门(Fact)</th>\n",
       "      <th>门店名称(Fact)</th>\n",
       "      <th>门店等级(Fact)</th>\n",
       "      <th>区域类别(Fact)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>44648</th>\n",
       "      <td>2023-06-09</td>\n",
       "      <td>2023-06</td>\n",
       "      <td>2023</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>24</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>工作日</td>\n",
       "      <td>华为</td>\n",
       "      <td>...</td>\n",
       "      <td>16041.0592</td>\n",
       "      <td>7808.442093</td>\n",
       "      <td>7576.399987</td>\n",
       "      <td>234253.2628</td>\n",
       "      <td>2765385.9954</td>\n",
       "      <td>192</td>\n",
       "      <td>1467</td>\n",
       "      <td>1589</td>\n",
       "      <td>1740</td>\n",
       "      <td>1754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               日期       年月     年  月 半年 周序列 周内第几天 日期等级 日等级名称 企业体系  ...  \\\n",
       "44648  2023-06-09  2023-06  2023  6  1  24     6    1   工作日   华为  ...   \n",
       "\n",
       "               毛利        毛利月日均        毛利年日均        毛利月合计         毛利年合计  \\\n",
       "44648  16041.0592  7808.442093  7576.399987  234253.2628  2765385.9954   \n",
       "\n",
       "      日期(Fact) 企业部门(Fact) 门店名称(Fact) 门店等级(Fact) 区域类别(Fact)  \n",
       "44648      192       1467       1589       1740       1754  \n",
       "\n",
       "[1 rows x 34 columns]"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"预测值: %f\" % Model.predict(DenseVector([192, 1467, 1589, 1740, 1754])))\n",
    "PDF_Store_Profit_ML[(PDF_Store_Profit_ML[\"日期\"] == \"2023-06-09\") & (PDF_Store_Profit_ML[\"门店名称\"] == \"四川青白江青江北路华为店\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "831b7df9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "预测值: 3563.430997\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>年月</th>\n",
       "      <th>年</th>\n",
       "      <th>月</th>\n",
       "      <th>半年</th>\n",
       "      <th>周序列</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>日期等级</th>\n",
       "      <th>日等级名称</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>...</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月日均</th>\n",
       "      <th>毛利年日均</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>毛利年合计</th>\n",
       "      <th>日期(Fact)</th>\n",
       "      <th>企业部门(Fact)</th>\n",
       "      <th>门店名称(Fact)</th>\n",
       "      <th>门店等级(Fact)</th>\n",
       "      <th>区域类别(Fact)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>258934</th>\n",
       "      <td>2021-03-16</td>\n",
       "      <td>2021-03</td>\n",
       "      <td>2021</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>12</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>工作日</td>\n",
       "      <td>形象</td>\n",
       "      <td>...</td>\n",
       "      <td>4584.422</td>\n",
       "      <td>4155.305867</td>\n",
       "      <td>4733.694515</td>\n",
       "      <td>128814.4819</td>\n",
       "      <td>1727798.4983</td>\n",
       "      <td>1020</td>\n",
       "      <td>1468</td>\n",
       "      <td>1693</td>\n",
       "      <td>1739</td>\n",
       "      <td>1754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                日期       年月     年  月 半年 周序列 周内第几天 日期等级 日等级名称 企业体系  ...  \\\n",
       "258934  2021-03-16  2021-03  2021  3  1  12     3    1   工作日   形象  ...   \n",
       "\n",
       "              毛利        毛利月日均        毛利年日均        毛利月合计         毛利年合计  \\\n",
       "258934  4584.422  4155.305867  4733.694515  128814.4819  1727798.4983   \n",
       "\n",
       "       日期(Fact) 企业部门(Fact) 门店名称(Fact) 门店等级(Fact) 区域类别(Fact)  \n",
       "258934     1020       1468       1693       1739       1754  \n",
       "\n",
       "[1 rows x 34 columns]"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"预测值: %f\" % Model.predict(DenseVector([1020, 1468, 1693, 1739, 1754])))\n",
    "PDF_Store_Profit_ML[(PDF_Store_Profit_ML[\"日期\"] == \"2021-03-16\") & (PDF_Store_Profit_ML[\"门店名称\"] == \"重庆南川形象店\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "c9fd979b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "预测值: 12207.832536\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>日期</th>\n",
       "      <th>年月</th>\n",
       "      <th>年</th>\n",
       "      <th>月</th>\n",
       "      <th>半年</th>\n",
       "      <th>周序列</th>\n",
       "      <th>周内第几天</th>\n",
       "      <th>日期等级</th>\n",
       "      <th>日等级名称</th>\n",
       "      <th>企业体系</th>\n",
       "      <th>...</th>\n",
       "      <th>毛利</th>\n",
       "      <th>毛利月日均</th>\n",
       "      <th>毛利年日均</th>\n",
       "      <th>毛利月合计</th>\n",
       "      <th>毛利年合计</th>\n",
       "      <th>日期(Fact)</th>\n",
       "      <th>企业部门(Fact)</th>\n",
       "      <th>门店名称(Fact)</th>\n",
       "      <th>门店等级(Fact)</th>\n",
       "      <th>区域类别(Fact)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>197503</th>\n",
       "      <td>2021-10-03</td>\n",
       "      <td>2021-10</td>\n",
       "      <td>2021</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>节假日</td>\n",
       "      <td>形象</td>\n",
       "      <td>...</td>\n",
       "      <td>8357.1199</td>\n",
       "      <td>10913.150538</td>\n",
       "      <td>8675.900117</td>\n",
       "      <td>338307.6667</td>\n",
       "      <td>3166703.5429</td>\n",
       "      <td>793</td>\n",
       "      <td>1463</td>\n",
       "      <td>1651</td>\n",
       "      <td>1743</td>\n",
       "      <td>1754</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 34 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                日期       年月     年   月 半年 周序列 周内第几天 日期等级 日等级名称 企业体系  ...  \\\n",
       "197503  2021-10-03  2021-10  2021  10  2  40     1    3   节假日   形象  ...   \n",
       "\n",
       "               毛利         毛利月日均        毛利年日均        毛利月合计         毛利年合计  \\\n",
       "197503  8357.1199  10913.150538  8675.900117  338307.6667  3166703.5429   \n",
       "\n",
       "       日期(Fact) 企业部门(Fact) 门店名称(Fact) 门店等级(Fact) 区域类别(Fact)  \n",
       "197503      793       1463       1651       1743       1754  \n",
       "\n",
       "[1 rows x 34 columns]"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(\"预测值: %f\" % Model.predict(DenseVector([793, 1463, 1651, 1743, 1754])))\n",
    "PDF_Store_Profit_ML[(PDF_Store_Profit_ML[\"日期\"] == \"2021-10-03\") & (PDF_Store_Profit_ML[\"门店名称\"] == \"四川崇州昌明路形象店\")]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db081403",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "54c4b997",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+----------+\n",
      "|       Features(Raw)|毛利(取整)|\n",
      "+--------------------+----------+\n",
      "|[0.0,48.0,56.0,61...|    5000.0|\n",
      "|[0.0,48.0,56.0,63...|    7500.0|\n",
      "|[0.0,48.0,56.0,62...|    9000.0|\n",
      "|[0.0,48.0,56.0,62...|    5500.0|\n",
      "|[0.0,48.0,56.0,61...|    5000.0|\n",
      "+--------------------+----------+\n",
      "only showing top 5 rows\n",
      "\n",
      "+--------------------+----------+\n",
      "|            features|毛利(取整)|\n",
      "+--------------------+----------+\n",
      "|[0.0,0.3987558290...|    5000.0|\n",
      "|[0.0,0.3934294066...|    7500.0|\n",
      "|[0.0,0.3931126144...|    9000.0|\n",
      "|[0.0,0.3950918386...|    5500.0|\n",
      "|[0.0,0.3927439890...|    5000.0|\n",
      "+--------------------+----------+\n",
      "only showing top 5 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 逻辑回归 Step 1: 组合特征值。\n",
    "# --------------------------------------------------\n",
    "Assembler_for_Logi = VectorAssembler(inputCols=[\"年月(Fact)\", \"企业部门(Fact)\", \"日期等级(Fact)\", \"门店等级(Fact)\", \"区域类别(Fact)\"],\n",
    "                                     outputCol=\"Features(Raw)\") # -> 组装特征\n",
    "# --------------------------------------------------\n",
    "Raw_Feat_for_Logi = Assembler_for_Logi.transform(SDF_Store_Profit_ML)\n",
    "# --------------------------------------------------\n",
    "Raw_Feat_for_Logi = Raw_Feat_for_Logi.select([\"Features(Raw)\", \"毛利(取整)\"])\n",
    "# --------------------------------------------------\n",
    "Raw_Feat_for_Logi.show(5)\n",
    "\n",
    "# 逻辑回归 Step 2: 特征值正则化。\n",
    "# --------------------------------------------------\n",
    "Normalizer_for_Logi = Normalizer().setInputCol(\"Features(Raw)\").setOutputCol(\"features\").setP(2.0)\n",
    "Feat_for_Logi = Normalizer_for_Logi.transform(Raw_Feat_for_Logi)\n",
    "Feat_for_Logi = Feat_for_Logi.select([\"features\", \"毛利(取整)\"])\n",
    "Feat_for_Logi.show(5)\n",
    "\n",
    "Feat_for_Logi.persist(StorageLevel.MEMORY_AND_DISK).count()\n",
    "\n",
    "# 逻辑回归 Step 3: 训练回归模型。\n",
    "# --------------------------------------------------\n",
    "Lr = LogisticRegression().setLabelCol(\"毛利(取整)\")\\\n",
    "                         .setFeaturesCol(\"features\")\\\n",
    "                         .setMaxIter(10)\\\n",
    "                         .setRegParam(0.3)\\\n",
    "                         .setElasticNetParam(0.8) # -> 设置弹性网络参数: L1 正则和 L2 正则联合使用。\n",
    "Model = Lr.fit(Feat_for_Logi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "49a15ae5",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'VectorAssembler' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_16196\\1625924929.py\u001b[0m in \u001b[0;36m<cell line: 3>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 逻辑回归 Step 4: 预测。\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;31m# --------------------------------------------------\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m Assembler_for_Predict = VectorAssembler(inputCols=[\"年月(Fact)\", \"企业部门(Fact)\", \"日期等级(Fact)\", \"门店等级(Fact)\", \"区域类别(Fact)\"],\n\u001b[0m\u001b[0;32m      4\u001b[0m                                         outputCol=\"Features(Raw)\") # -> 组装特征\n\u001b[0;32m      5\u001b[0m \u001b[1;31m# --------------------------------------------------\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'VectorAssembler' is not defined"
     ]
    }
   ],
   "source": [
    "# 逻辑回归 Step 4: 预测。\n",
    "# --------------------------------------------------\n",
    "Assembler_for_Predict = VectorAssembler(inputCols=[\"年月(Fact)\", \"企业部门(Fact)\", \"日期等级(Fact)\", \"门店等级(Fact)\", \"区域类别(Fact)\"],\n",
    "                                        outputCol=\"Features(Raw)\") # -> 组装特征\n",
    "# --------------------------------------------------\n",
    "Raw_Feat_for_Predict = Assembler_for_Predict.transform(SDF_Store_Profit_NA)\n",
    "Normalizer_for_Predict = Normalizer().setInputCol(\"Features(Raw)\").setOutputCol(\"features\").setP(2.0)\n",
    "Feat_for_Predict = Normalizer_for_Predict.transform(Raw_Feat_for_Logi)\n",
    "# --------------------------------------------------\n",
    "Result = Model.transform(Feat_for_Predict)\n",
    "Result.select([\"门店名称\", \"门店等级\", \"features\", \"rawPrediction\", \"probability\", \"prediction\"]).show()\n",
    "\n",
    "# 逻辑回归 Step 4: 评估回归模型。\n",
    "# --------------------------------------------------\n",
    "Evaluator = pyspark.ml.evaluation.MulticlassClassificationEvaluator().setLabelCol(\"毛利(取整)\")\\\n",
    "                                                                     .setPredictionCol(\"prediction\")\\\n",
    "                                                                     .setMetricName(\"accuracy\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40f6a967",
   "metadata": {},
   "outputs": [],
   "source": [
    "print(\"Accuracy: \" % Evaluator.evaluate(Result))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c5ab501",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
